from students.course.course import Course
from students.course.ubtil import Gonju


class CourseDB:
    def __init__(self):
        host = '127.0.0.1'
        port = 3306
        user = 'root'
        passwd = '123456'
        db = 't_course'
        self.dbobj,self.cursor=Gonju.openDB(host,port,user,passwd,db)
    def delete(self,course_no):
        sql=f"delete from t_course where course_no='{course_no}'"
        result=Gonju.writeDB(sql,self.cursor)
        self.dbobj.commit()
        return result
    def add(self,course):
        sql = f'insert into t_course(course_no,course_name,max_score,min_score)values("{course.course_no}","{course.course_name}",{course.max_score},{course.min_score})'
        result = Gonju.writeDB(sql, self.cursor)
        self.dbobj.commit()
    def update(self,course):
        sql = f'update t_course set course_no="{course.course_no}",course_name="{course.course_name}",max_score={course.max_score},min_score={course.min_score} where id={course.id}'
        result = Gonju.writeDB(sql, self.cursor)
        self.dbobj.commit()
    def searchOne(self,course_no=None,course_name=None):
        if course_no !=None :
            sql=f"select * from t_course where course_no like '%{course_no}%'"
        if course_name != None:
            sql = f"select * from t_course where course_name like '%{course_name}%'"
        result=Gonju.readALL(sql,self.cursor)
        return result
    def searchAll(self):
        sql=f"select * from t_course"
        result=Gonju.readALL(sql,self.cursor)
        return result

if __name__ == '__main__':
    c1=CourseDB()
    # c1.delete('st002')
    c2=Course()

    # c2.course_no = "st005"
    # c2.course_name = "buhao"
    # c2.max_score = 101
    # c2.min_score = 61
    # c1.add(c2)

    # c2.id=12
    # c2.course_no = "st007"
    # c2.course_name = "verygood"
    # c2.max_score = 101
    # c2.min_score = 61
    # c1.update(c2)
    res=c1.searchAll()
    print(res)